CREATE TABLE contable_Correlativo_Documento
(
	CorrelativoDocumentoId uniqueidentifier NOT NULL,
	DocumentoTipo VARCHAR(50),
	Contador uniqueidentifier,
	Serie VARCHAR(10),
	
	AgenciaId uniqueidentifier NOT NULL,
	PRIMARY KEY (CorrelativoDocumentoId),
	FOREIGN KEY (AgenciaId) REFERENCES common_Agencia(AgenciaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION
)  ;

CREATE TABLE contable_Documento_Impr
(
	DocumentoImprId uniqueidentifier NOT NULL,
	Documento VARCHAR(50),
	DocumentoTipo VARCHAR(50),
	TextLogo VARCHAR(50),
	TextEmpresa VARCHAR(50),
	TextAgencia VARCHAR(50),
	TextRuc VARCHAR(50),
	TextDireccion VARCHAR(50),
	TextTelef VARCHAR(50),
	TextDocTipo VARCHAR(50),
	TextDocNum VARCHAR(50),
	AgenciaId uniqueidentifier NOT NULL,
	PRIMARY KEY (DocumentoImprId),
	FOREIGN KEY (AgenciaId) REFERENCES common_Agencia(AgenciaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION
)  ;

CREATE TABLE contable_Doc_Impr_Temp
(
	DocImprTempId uniqueidentifier NOT NULL,
	Texto VARCHAR(150),
	Fila INT,
	UserId uniqueidentifier NULL,
	AgenciaId uniqueidentifier NULL,
	PRIMARY KEY (DocImprTempId),
	FOREIGN KEY (UserId) REFERENCES common_User(UserId),
	FOREIGN KEY (AgenciaId) REFERENCES common_Agencia(AgenciaId) 
) ;

CREATE TABLE contable_Caja_Ventanilla
(
	CajaVentanillaId uniqueidentifier NOT NULL,
	Nombre VARCHAR(50),
	TicketeraPropiaDisponible bit,
	Ip VARCHAR(50),
	Port VARCHAR(5),
	AgenciaId uniqueidentifier NOT NULL,
	PRIMARY KEY (CajaVentanillaId),
	FOREIGN KEY (AgenciaId) REFERENCES common_Agencia(AgenciaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION
)  ;
-- ALTER TABLE contable_Caja_Ventanilla ADD `Port` VARCHAR(5);

ALTER TABLE contable_Caja_Ventanilla ADD CajeroId uniqueidentifier;
ALTER TABLE contable_Caja_Ventanilla ADD  
FOREIGN KEY (CajeroId) REFERENCES common_User(UserId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION;
ALTER TABLE contable_Caja_Ventanilla ADD UNIQUE(AgenciaId,CajeroId);


CREATE TABLE contable_Caja_Ventanilla_Serie
(
	CajaVentanillaSerieId uniqueidentifier NOT NULL,
	Serie VARCHAR(10),
	DocumentoTipo VARCHAR(50),
        CajaVentanillaId uniqueidentifier NOT NULL,
	PRIMARY KEY (CajaVentanillaSerieId),
	FOREIGN KEY (CajaVentanillaId) REFERENCES contable_Caja_Ventanilla(CajaVentanillaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION
)  ;


CREATE TABLE contable_Caja_Operacion
(
	CajaOperacionId uniqueidentifier NOT NULL,
	CajaForm VARCHAR(50),
	Operacion VARCHAR(50),
	CargarAFavorCliente bit,
	CargarEnContraCliente bit,
	GenerarVenta bit, -- no usado
	CargarEnContraProveedor bit,-- no usado
	ObtenerCompra bit, -- no usado
	
	DocumentoImprId uniqueidentifier NOT NULL,
	AgenciaId uniqueidentifier NOT NULL,
	
	PRIMARY KEY (CajaOperacionId),
	FOREIGN KEY (DocumentoImprId) REFERENCES contable_Documento_Impr(DocumentoImprId) 
	ON DELETE NO ACTION
	ON UPDATE NO ACTION,	
	FOREIGN KEY (AgenciaId) REFERENCES common_Agencia(AgenciaId) 
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
) ;

CREATE TABLE contable_Caja_Operacion_Asiento
(
	CajaOperacionAsientoId uniqueidentifier NOT NULL,
	Glosa VARCHAR(50),
	-- Porc float,
	-- Monto float,
	DH VARCHAR(1),
	EsCtaCtrl bit,
	
	DefCuentaId uniqueidentifier NULL,
	DefCCId uniqueidentifier NULL,
	CajaOperacionId uniqueidentifier NOT NULL,

	PRIMARY KEY (CajaOperacionAsientoId),	
	FOREIGN KEY (DefCuentaId) REFERENCES common_Cuenta(CuentaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION,
	FOREIGN KEY (DefCCId) REFERENCES common_Centro_Costo(CentroCostoId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION,
	FOREIGN KEY (CajaOperacionId) REFERENCES contable_Caja_Operacion(CajaOperacionId) 
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
) ;
ALTER TABLE contable_Caja_Operacion_Asiento ADD EsCtaComision bit;
ALTER TABLE contable_Caja_Operacion_Asiento ADD EsCtaPrinc bit;

CREATE TABLE contable_Caja_Voucher
(
	CajaVoucherId uniqueidentifier NOT NULL,
	Fecha DATETIME,
	Voucher VARCHAR(50),
	Libro VARCHAR(50),
	EstaAbierto bit,

        EjercicioContableId uniqueidentifier NOT NULL,
        AgenciaId uniqueidentifier NOT NULL,
	PRIMARY KEY (CajaVoucherId),
	FOREIGN KEY (EjercicioContableId) REFERENCES common_Ejercicio_Contable(EjercicioContableId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION,	
	FOREIGN KEY (AgenciaId) REFERENCES common_Agencia(AgenciaId) 
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
)  ;


CREATE TABLE contable_Caja_Diario
(
	CajaDiarioId uniqueidentifier NOT NULL,
	Fecha DATETIME,
	Doc VARCHAR(50),
	InicialEfectivoSo float,
	InicialEfectivoDo float,
	InicialMaterialGr float,
	EstaAbierto bit,
	-- TotalIngresoSo float,
	-- TotalIngresoDo float,
	-- TotalIngresoGr float,
	-- TotalEgresoSo float,
	-- TotalEgresoDo float,
	-- TotalEgresoGr float,
	-- FinalEfectivoSo float,
	-- FinalEfectivoDo float,
	-- FinalMaterialGr float,

        -- EjercicioContableId uniqueidentifier NOT NULL,
        CajaVentanillaId uniqueidentifier NOT NULL,
	DefaultServidorImpesionId uniqueidentifier NULL,
	CajaVoucherId uniqueidentifier NOT NULL,
        UserId uniqueidentifier NOT NULL,
	PRIMARY KEY (CajaDiarioId),
	FOREIGN KEY (CajaVentanillaId) REFERENCES contable_Caja_Ventanilla(CajaVentanillaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION,
	FOREIGN KEY (DefaultServidorImpesionId) REFERENCES contable_Caja_Ventanilla(CajaVentanillaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION,
	FOREIGN KEY (CajaVoucherId) REFERENCES contable_Caja_Voucher(CajaVoucherId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION,
	FOREIGN KEY (UserId) REFERENCES common_User(UserId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION
)  ;
-- ALTER TABLE contable_Caja_Diario change Doc DocNuevoNombre VARCHAR(50);

CREATE TABLE contable_Caja
(
	CajaId uniqueidentifier NOT NULL,
	IE VARCHAR(1),
	Fecha DATETIME,
	DocTipo VARCHAR(50),
	Serie VARCHAR(50),
	DocFiscal VARCHAR(50),
	Glosa VARCHAR(50),
        Beneficiario VARCHAR(50),
	
	
	ITotalSo float,
	ETotalSo float,
	
	
	ITotalDo float,
	ETotalDo float,
	
	
	ITotalGr float,
	ETotalGr float,
	
	EstaAnulado bit,
	EstaContabilizado bit,
        CajaOperacionId uniqueidentifier NULL,
        
	
	ClienteId uniqueidentifier,
        ProveedorId uniqueidentifier,
        -- CompraId uniqueidentifier,
        CajaVoucherId uniqueidentifier NOT NULL,
        CajaDiarioId uniqueidentifier NOT NULL,
        EjercicioContableId uniqueidentifier NOT NULL,
        AgenciaId uniqueidentifier NOT NULL,
        UserId uniqueidentifier NOT NULL,
        
	DocRefTipo VARCHAR(50),
	DocRefSerie VARCHAR(50),
	DocRefNum VARCHAR(50),
	DocRefFecha DATETIME,
	
	PRIMARY KEY (CajaId),
	FOREIGN KEY (CajaOperacionId) REFERENCES contable_Caja_Operacion(CajaOperacionId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION,
	FOREIGN KEY (ClienteId) REFERENCES common_Cliente(ClienteId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION,
	FOREIGN KEY (ProveedorId) REFERENCES common_Proveedor(ProveedorId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION,
	-- FOREIGN KEY (CompraId) REFERENCES contable_Entrada(EntradaId) 
	-- ON DELETE NO ACTION 
	-- ON UPDATE NO ACTION,
	FOREIGN KEY (CajaVoucherId) REFERENCES contable_Caja_Voucher(CajaVoucherId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION,
	FOREIGN KEY (CajaDiarioId) REFERENCES contable_Caja_Diario(CajaDiarioId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION,
	FOREIGN KEY (EjercicioContableId) REFERENCES common_Ejercicio_Contable(EjercicioContableId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION,
	FOREIGN KEY (AgenciaId) REFERENCES common_Agencia(AgenciaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION,
	FOREIGN KEY (UserId) REFERENCES common_User(UserId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION
)  ;



CREATE TABLE contable_Caja_Asiento
(
	CajaAsientoId uniqueidentifier NOT NULL,
	Glosa VARCHAR(50),
	
	DebeSo float,
	HaberSo float,
	
	
	DebeDo float,
	HaberDo float,
	
	
	DebeGr float,
	HaberGr float,
	Doc VARCHAR(50),
	DH VARCHAR(1),
	EsCtaCtrl bit,
	
        CajaId uniqueidentifier NOT NULL,
	CuentaId uniqueidentifier NULL,
	CCId uniqueidentifier NULL,

	PRIMARY KEY (CajaAsientoId),
	FOREIGN KEY (CajaId) REFERENCES contable_Caja(CajaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION,
	FOREIGN KEY (CuentaId) REFERENCES common_Cuenta(CuentaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION,
	FOREIGN KEY (CCId) REFERENCES common_Centro_Costo(CentroCostoId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION
)  ;


CREATE TABLE contable_Cliente_Movimiento
(
	ClienteMovimientoId uniqueidentifier NOT NULL,
	Fecha DATETIME,
	DocFiscal VARCHAR(50),
	Glosa VARCHAR(50),
	DebitoSo float,
	CreditoSo float,
	
	DebitoDo float,
	CreditoDo float,
	
	DebitoGr float,
	CreditoGr float,
	
	EjercicioContableId uniqueidentifier NOT NULL,
	ClienteId uniqueidentifier NOT NULL,
	UserId uniqueidentifier NOT NULL,
	PRIMARY KEY (ClienteMovimientoId),
	FOREIGN KEY (EjercicioContableId) REFERENCES common_Ejercicio_Contable(EjercicioContableId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION,
	FOREIGN KEY (ClienteId) REFERENCES common_Cliente(ClienteId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION,
	FOREIGN KEY (UserId) REFERENCES common_User(UserId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION
)  ;



CREATE TABLE contable_Giro
(
	GiroId uniqueidentifier NOT NULL,
	FechaDeposito DATETIME,
	DocFiscalDeposito VARCHAR(50),
	RemitenteNombres VARCHAR(50),
	RemitenteDni VARCHAR(50),
	BeneficiarioNombres VARCHAR(50),
	BeneficiarioDni VARCHAR(50),
	BeneficiarioDirec VARCHAR(50),
	Beneficiario2Nombres VARCHAR(50),
	Beneficiario2Dni VARCHAR(50),
	Beneficiario2Direc VARCHAR(50),
	ImporteSo float,
	ComisionSo float,
	Total float,
	LugarPago VARCHAR(50),
	FueRetirado bit,
	FechaRetiro DATETIME,
	DocFiscalRetiro VARCHAR(50),

	
        AgenciaDestinoId uniqueidentifier NULL,
        CajaIngresoId uniqueidentifier NOT NULL,
        CajaEgresoId uniqueidentifier NULL,
        AgenciaId uniqueidentifier NOT NULL,
	PRIMARY KEY (GiroId),
	FOREIGN KEY (AgenciaDestinoId) REFERENCES common_Agencia(AgenciaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION,
	FOREIGN KEY (CajaIngresoId) REFERENCES contable_Caja(CajaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION,
	FOREIGN KEY (CajaEgresoId) REFERENCES contable_Caja(CajaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION,
	FOREIGN KEY (AgenciaId) REFERENCES common_Agencia(AgenciaId) 
	ON DELETE NO ACTION 
	ON UPDATE NO ACTION
)  ;